package lib;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import aop.db.Table;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author gnail
 * @since 2015/8/18
 */
public class MySQLHelper {

    private static final String dbUserName = Configuration.get("db.user");
    private static final String dbUserPassword = Configuration.get("db.pass");
    private static final String dbUrl = Configuration.get("db.url");
    private static AtomicInteger nextIdx = new AtomicInteger(-1);
    private static Map<String, List<String>> columnNameCache = new HashMap<String, List<String>>();
    private static List<DataSource> readOnlyDataSources = new ArrayList<DataSource>();
    private static HikariDataSource masterDataSource = null;
    static {
        init();
        initReadOnly();
    }

    private static void init() {
        if (masterDataSource != null) {
            masterDataSource.close();
        }
        masterDataSource = initDataSource(dbUrl);
    }

    private static void initReadOnly() {
        for (DataSource dataSource : readOnlyDataSources) {
            if (dataSource instanceof HikariDataSource) {
                ((HikariDataSource)dataSource).close();
            }
        }
        String readOnlyUrls = "";
        String[] urls = StringUtils.split(readOnlyUrls, ",");
        for (String url : urls) {
            HikariDataSource ds = initDataSource(url);
            if (ds != null) readOnlyDataSources.add(ds);
        }
    }

    private static HikariDataSource initDataSource(String dbUrl) {
        HikariDataSource ds = null;
        try {
            HikariConfig config = new HikariConfig();
            config.setJdbcUrl(dbUrl);
            config.setDriverClassName("com.mysql.jdbc.Driver");
            config.setUsername(dbUserName);
            config.setPassword(dbUserPassword);
            config.setAutoCommit(false);
            config.setConnectionTimeout(30 * 1000);
            config.setIdleTimeout(60 * 1000);
            config.setMaximumPoolSize(10);
            config.addDataSourceProperty("cachePrepStmts", "true");
            config.addDataSourceProperty("prepStmtCacheSize", "250");
            config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
            ds = new HikariDataSource(config);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ds;
    }

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * <p>
     * 定义连接mysql的驱动器.
     * <p>
     * @return connection:数据库连接
     */
    public static Connection getMasterConnection() {
        if (masterDataSource == null) init();
        try {
            return masterDataSource.getConnection();
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
    }
    public static Connection getReadOnlyConnection() {
        if (readOnlyDataSources == null || readOnlyDataSources.isEmpty()) initReadOnly();
        DataSource ds = null;
        for (;;) {
            int current = nextIdx.get();
            int next = current >= readOnlyDataSources.size() - 1 ? 0 : current + 1;
            if (nextIdx.compareAndSet(current, next)) {
                if (next < readOnlyDataSources.size()) {
                    ds = readOnlyDataSources.get(next);
                }
                break;
            }
        }
        // 如果从库都不存在，只好用主库来读了
        if (ds == null) {
            return getMasterConnection();
        }
        try {
            return ds.getConnection();
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
    }
    /**
     * Execute an SQL query
     * @param sql sql
     * @return The query resultSet
     */
    public static <T> T executeQuery(MySQLCallback<T> callback, String sql, Object ... params) throws Exception {
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            connection = getReadOnlyConnection();
            pst = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            int index = 0;

            if (params != null) {
                for (Object param : params) {
                    pst.setObject(++index, param);
                }
            }

            rs = pst.executeQuery();
            if (callback != null) {
                return callback.executeQuery(rs);
            }
        } finally {
            close(rs);
            close(pst);
            close(connection);
        }
        return null;
    }

    public static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Statement st) {
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void rollback(Connection connection) {
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static List<String> getColumns(ResultSet rs, String sql) {
        String s = StringUtils.substringBefore(sql, "where").trim();
        List<String> columns = columnNameCache.get(s);
        if (columns == null || columns.isEmpty()) {
            columns = new ArrayList<String>();
            try {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    columns.add(metaData.getColumnName(i));
                }
                columnNameCache.put(s, columns);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return columns;
    }

    public interface MySQLCallback<T> {
        T executeQuery(final ResultSet rs) throws Exception;
    }

    @SuppressWarnings("rawtypes")
	public static List<Map> find(final String sql, Object... args) {
        try {
            return executeQuery(new MySQLCallback<List<Map>>() {
                @Override
                public List<Map> executeQuery(ResultSet rs) throws Exception {
                    List<String> columns = getColumns(rs, sql);
                    List<Map> data = new ArrayList<Map>();
                    while (rs.next()) {
                        Map<String, Object> map = new HashMap<String, Object>();
                        for (int i = 1; i <= columns.size(); i++) {
                            map.put(columns.get(i - 1), rs.getObject(i));
                        }
                        data.add(map);
                    }
                    return data;
                }
            }, sql, args);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public static <T> List<T> find(final Class<T> cls, String sql, Object... args) {
    	String table = cls.getAnnotation(Table.class).name().equals("")?cls.getSimpleName():cls.getAnnotation(Table.class).name();
        final String realSql = String.format("select * from %s where %s", table, sql);
        try {
            return executeQuery(rs -> {
                List<String> columns = getColumns(rs, realSql);
                List<T> data = new ArrayList<>();
                while (rs.next()) {
                    Map<String, Object> map = new HashMap<>();
                    for (int i = 1; i <= columns.size(); i++) {
                        map.put(columns.get(i - 1), rs.getObject(i));
                    }
                    data.add(JsonHelper.restore(map, cls));
                }
                return data;
            }, realSql, args);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public static List<DataSource> getReadOnlyDataSources() {
        return readOnlyDataSources;
    }

    public static void withTransaction(Execute execute) throws SQLException {
        Connection connection = null;
        try {
            connection = getMasterConnection();
            execute.connection = connection;
            execute.call();
            connection.commit();
        }  catch (Exception e) {
            rollback(connection);
            throw new SQLException(e);
        }  finally {
            close(connection);
        }
    }
}